package cn.wizzer.app.web.modules.controllers.platform.bus.reports;

import cn.wizzer.app.bus.modules.models.YJbxx;
import cn.wizzer.app.bus.modules.services.YJbxxService;
import cn.wizzer.app.sys.modules.models.Sys_user;
import cn.wizzer.app.sys.modules.services.SysDictService;
import cn.wizzer.app.sys.modules.services.SysUnitService;
import cn.wizzer.app.web.commons.utils.DateUtil;
import cn.wizzer.app.web.commons.utils.StringUtil;
import cn.wizzer.framework.base.Result;
import cn.wizzer.framework.page.Pagination;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresAuthentication;
import org.nutz.dao.Cnd;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.dao.sql.Sql;
import org.nutz.integration.json4excel.J4E;
import org.nutz.integration.json4excel.J4EColumn;
import org.nutz.integration.json4excel.J4EConf;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.nutz.lang.util.NutMap;
import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.nutz.mvc.annotation.At;
import org.nutz.mvc.annotation.Ok;
import org.nutz.mvc.annotation.Param;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@IocBean
@At("/platform/bus/reports/sReport")
public class SReportController {


    /*报告综合查询*/
    private static final Log log = Logs.get();
    @Inject
    private YJbxxService yjbxxService;
    @Inject
    private SysDictService sysDictService;
    @Inject
    private SysUnitService sysUnitService;


    @At("/")
    @Ok("beetl:/platform/bus/reports/sReport/index.html")
    @RequiresAuthentication
    public void index(HttpServletRequest req) {
        req.setAttribute("jylxMap", JSONObject.fromObject(sysDictService.getCodeMap("jylx")));
        req.setAttribute("lcztMap", JSONObject.fromObject(sysDictService.getCodeMap("lczt")));
        req.setAttribute("lzztMap", JSONObject.fromObject(sysDictService.getCodeMap("lzzt")));
        req.setAttribute("lcztOptions", JSONArray.fromObject(sysDictService.dictCodeTree("lczt").stream().filter(t->Integer.valueOf(t.get("value").toString())>0).collect(Collectors.toList())));
        req.setAttribute("lzztOptions", JSONArray.fromObject(sysDictService.dictCodeTree("lzzt")));
        req.setAttribute("zhMap", JSONObject.fromObject(sysDictService.getIdMap("zh")));
        req.setAttribute("yplxMap", JSONObject.fromObject(sysDictService.getCodeMap("yplx")));
        req.setAttribute("xzqhMap", JSONObject.fromObject(sysDictService.getIdMap("xzqh")));
        req.setAttribute("ypczMap", JSONObject.fromObject(sysDictService.getIdMap("ypcz")));


        req.setAttribute("startDate", DateUtil.getMonthBegin());
        req.setAttribute("endDate", DateUtil.getMonthEnd());
        List<NutMap> listSelect = new ArrayList<>();
        //基本信息
/*
        listSelect.add(NutMap.NEW().addv("key", "jyksbh").addv("label", "检验科室").addv("condition","drop").addv("id", "0").addv("options",JSONArray.fromObject(sysUnitService.getJybmTree())));
*/
        listSelect.add(NutMap.NEW().addv("key", "zh").addv("label", "项目类别").addv("condition","drop").addv("id", "1").addv("options",JSONArray.fromObject(sysDictService.dictTree("zh"))));
        listSelect.add(NutMap.NEW().addv("key", "yplx").addv("label", "产品类型").addv("condition","dropLike").addv("id", "2").addv("options",JSONArray.fromObject(sysDictService.dictCodeTree("yplx"))));
        listSelect.add(NutMap.NEW().addv("key", "ypdj").addv("label", "样品等级").addv("condition","like").addv("id", "3"));
        listSelect.add(NutMap.NEW().addv("key", "ypzt").addv("label", "样品特性/状态").addv("condition","like").addv("id", "4"));
        listSelect.add(NutMap.NEW().addv("key", "bysl").addv("label", "备样数量").addv("condition","number").addv("id", "5"));
        listSelect.add(NutMap.NEW().addv("key", "jysl").addv("label", "检样数量").addv("condition","number").addv("id", "6"));
        listSelect.add(NutMap.NEW().addv("key", "jylx").addv("label", "检验类别").addv("condition","drop").addv("id", "7").addv("options",JSONArray.fromObject(sysDictService.dictCodeTree("jylx"))));
        listSelect.add(NutMap.NEW().addv("key", "szcs").addv("label", "所在城市").addv("condition","drop").addv("id", "8").addv("options",JSONArray.fromObject(sysDictService.dictTreeChild("xzqh"))));
        listSelect.add(NutMap.NEW().addv("key", "scrqpc").addv("label", "原编号/生产日期").addv("condition","like").addv("id", "9"));
        listSelect.add(NutMap.NEW().addv("key", "ggxh").addv("label", "规格型号").addv("condition","like").addv("id", "10"));
        listSelect.add(NutMap.NEW().addv("key", "cyry").addv("label", "企业联系人").addv("condition","like").addv("id", "11"));
        listSelect.add(NutMap.NEW().addv("key", "jyfdb").addv("label", "企业联系方式").addv("condition","like").addv("id", "12"));
        listSelect.add(NutMap.NEW().addv("key", "sb").addv("label", "商标").addv("condition","like").addv("id", "13"));
        listSelect.add(NutMap.NEW().addv("key", "cydw").addv("label", "抽样单位").addv("condition","like").addv("id", "14"));
        listSelect.add(NutMap.NEW().addv("key", "cyryBy").addv("label", "抽样人员").addv("condition","like").addv("id", "15"));
        listSelect.add(NutMap.NEW().addv("key", "cyrq").addv("label", "抽样日期").addv("condition","date").addv("id", "16"));
        listSelect.add(NutMap.NEW().addv("key", "cydbh").addv("label", "抽样单编号").addv("condition","like").addv("id", "17"));
        listSelect.add(NutMap.NEW().addv("key", "cyjs").addv("label", "抽样基数").addv("condition","like").addv("id", "18"));
        listSelect.add(NutMap.NEW().addv("key", "cydd").addv("label", "抽样地点").addv("condition","like").addv("id", "19"));
        //单位信息
        listSelect.add(NutMap.NEW().addv("key", "wtdw").addv("label", "委托单位").addv("condition","like").addv("id", "20"));
        listSelect.add(NutMap.NEW().addv("key", "wtdwdz").addv("label", "委托单位地址").addv("condition","like").addv("id", "21"));
        listSelect.add(NutMap.NEW().addv("key", "wtdwlxr").addv("label", "委托单位法人").addv("condition","like").addv("id", "22"));
        listSelect.add(NutMap.NEW().addv("key", "wtdwsj").addv("label", "委托单位手机").addv("condition","like").addv("id", "23"));
        listSelect.add(NutMap.NEW().addv("key", "wtdwdh").addv("label", "委托单位电话").addv("condition","like").addv("id", "24"));
        listSelect.add(NutMap.NEW().addv("key", "wtdwyb").addv("label", "委托单位邮编").addv("condition","like").addv("id", "25"));
        listSelect.add(NutMap.NEW().addv("key", "sjdw").addv("label", "受检单位").addv("condition","like").addv("id", "26"));
        listSelect.add(NutMap.NEW().addv("key", "sjdwdz").addv("label", "受检单位地址").addv("condition","like").addv("id", "27"));
        listSelect.add(NutMap.NEW().addv("key", "sjdwlxr").addv("label", "受检单位法人").addv("condition","like").addv("id", "28"));
        listSelect.add(NutMap.NEW().addv("key", "sjdwsj").addv("label", "受检单位手机").addv("condition","like").addv("id", "29"));
        listSelect.add(NutMap.NEW().addv("key", "sjdwdh").addv("label", "受检单位电话").addv("condition","like").addv("id", "30"));
        listSelect.add(NutMap.NEW().addv("key", "sjdwyb").addv("label", "受检单位邮编").addv("condition","like").addv("id", "31"));
        listSelect.add(NutMap.NEW().addv("key", "scdw").addv("label", "生产单位").addv("condition","like").addv("id", "32"));
        listSelect.add(NutMap.NEW().addv("key", "scdwdz").addv("label", "生产单位地址").addv("condition","like").addv("id", "33"));
        listSelect.add(NutMap.NEW().addv("key", "scdwlxr").addv("label", "受检单位法人").addv("condition","like").addv("id", "34"));
        listSelect.add(NutMap.NEW().addv("key", "scdwsj").addv("label", "生产单位手机").addv("condition","like").addv("id", "35"));
        listSelect.add(NutMap.NEW().addv("key", "scdwdh").addv("label", "生产单位电话").addv("condition","like").addv("id", "36"));
        listSelect.add(NutMap.NEW().addv("key", "scdwyb").addv("label", "生产单位邮编").addv("condition","like").addv("id", "37"));
        //检验信息
        listSelect.add(NutMap.NEW().addv("key", "jyxm").addv("label", "检验项目").addv("condition","like").addv("id", "38"));
        listSelect.add(NutMap.NEW().addv("key", "jyyj").addv("label", "检验依据").addv("condition","like").addv("id", "39"));
        listSelect.add(NutMap.NEW().addv("key", "yhxtk").addv("label", "样品处置").addv("condition","drop").addv("id", "40").addv("options",JSONArray.fromObject(sysDictService.dictTree("ypcz"))));
        listSelect.add(NutMap.NEW().addv("key", "jyhth").addv("label", "检验合同号").addv("condition","like").addv("id", "41"));
        listSelect.add(NutMap.NEW().addv("key", "jcfyry").addv("label", "检查封样人员").addv("condition","like").addv("id", "42"));
        /*listSelect.add(NutMap.NEW().addv("key", "bgfsfs").addv("label", "报告领取").addv("condition","drop").addv("id", "43"));*/
        listSelect.add(NutMap.NEW().addv("key", "dyrq").addv("label", "到样日期").addv("condition","date").addv("id", "44"));
        listSelect.add(NutMap.NEW().addv("key", "wcqx").addv("label", "完成期限").addv("condition","date").addv("id", "45"));
        listSelect.add(NutMap.NEW().addv("key", "bz").addv("label", "备注").addv("condition","like").addv("id", "46"));
        listSelect.add(NutMap.NEW().addv("key", "tssm").addv("label", "特殊说明").addv("condition","like").addv("id", "47"));
        //报告信息
        listSelect.add(NutMap.NEW().addv("key", "jyrq").addv("label", "检验日期").addv("condition","date").addv("id", "48"));
        List<NutMap> treeList = new ArrayList<>();
        NutMap map = NutMap.NEW().addv("value", "0").addv("label", "合格");
        treeList.add(map);
        NutMap map1 = NutMap.NEW().addv("value", "1").addv("label", "不合格");
        treeList.add(map1);
        NutMap map2 = NutMap.NEW().addv("value", "2").addv("label", "不判定");
        treeList.add(map2);
        listSelect.add(NutMap.NEW().addv("key", "jyjg").addv("label", "检验结果").addv("condition","drop").addv("id", "49").addv("options",JSONArray.fromObject(treeList)));
        listSelect.add(NutMap.NEW().addv("key", "jyjl").addv("label", "检验结论").addv("condition","like").addv("id", "50"));
        listSelect.add(NutMap.NEW().addv("key", "jyqksm").addv("label", "检验情况说明").addv("condition","like").addv("id", "51"));
        listSelect.add(NutMap.NEW().addv("key", "djsj").addv("label", "登记日期").addv("condition","date").addv("id", "52"));
        listSelect.add(NutMap.NEW().addv("key", "djr").addv("label", "登记人").addv("condition","like").addv("id", "53"));
        //流程信息
        req.setAttribute("listSelect", JSONArray.fromObject(listSelect));


    }

    @At
    @Ok("json")
    @RequiresAuthentication
    public Object data(@Param("searchKeyword") String searchKeyword, @Param("bgbh") String bgbh,@Param("lczt") String lczt, @Param("lzzt") String lzzt,
                       @Param("listSelect") String listSelect,@Param("pageNumber") int pageNumber,
                       @Param("pageSize") int pageSize,
                       @Param("pageOrderName") String pageOrderName, @Param("pageOrderBy") String pageOrderBy) {
        try {

            List<JSONObject> list = JSONArray.fromObject(listSelect);

            String sql = " select * from  (select j.id,j.jyksbh,j.jyks,j.zh,j.yplx,j.ypdj,j.ypzt,j.bysl,j.jysl,j.szcs,j.scrqpc,j.sb,j.ggxh,j.cyryBy,j.cyry,j.cyrq,j.cydbh, " +
                    " j.cydw,j.cyjs,j.cydd,j.jyfdb,j.wtdw,j.wtdwdz,j.wtdwlxr,j.wtdwsj,j.wtdwdh,j.wtdwyb,j.sjdw,j.sjdwdz,j.sjdwlxr,j.sjdwsj, " +
                    " j.sjdwdh,j.sjdwyb,j.scdw,j.scdwdz,j.scdwlxr,j.scdwsj,j.scdwdh,j.scdwyb,j.jyxm,j.jyyj,j.yhxtk,j.jylx,j.jyhth,j.jcfyry, " +
                    " j.dyrq,j.wcqx,j.bz,j.tssm,j.jyrq,j.jyjl,j.jyqksm,j.dyzt,ur.username as djr,FROM_UNIXTIME(j.opAt) as djsj, " +
                    " j.lczt,j.lzzt,j.ypmc,j.ypbh,j.bgbh,j.jyjg,j.bgfsfs,j.status,j.unit," +

                    " lc.bzr,lc.jyry,lc.bzsj,lc.shr,lc.shsj,lc.pzr,lc.pzsj, " +

                    " lzz.yjr,lzz.tyr,lzz.tysj,lzz.ghr,lzz.ghsj " +
                    " from y_jbxx j  " +
                    " left join ( " +
                    " select jbxxId, " +
                    " GROUP_CONCAT(case when lczt=2 then nextspr end) as bzr, " +
                    " GROUP_CONCAT(case when lczt=2 then jyry end) as jyry, " +
                    " GROUP_CONCAT(case when lczt=5 then FROM_UNIXTIME(opAt) end) as bzsj, " +
                    " GROUP_CONCAT(case when lczt=5 then nextspr end) as shr, " +
                    " GROUP_CONCAT(case when lczt=7 then FROM_UNIXTIME(opAt) end) as shsj, " +
                    " GROUP_CONCAT(case when lczt=7 then nextspr end) as pzr, " +
                    " GROUP_CONCAT(case when lczt=9 then FROM_UNIXTIME(opAt) end) as pzsj " +
                    " from y_jbxx_lcxx  where delFlag=0 group by jbxxId " +
                    " ) lc on j.id = lc.jbxxId " +
                    " left join sys_user ur on j.opBy=ur.id  " +
                    " left join ( " +
                    " select lz.jbxxId, " +
                    " GROUP_CONCAT(case when lz.lzzt=1 then concat('移交人：',u.username,'-移交时间：',lz.lzAt,'-接收人：' ,lz.nextspr) end,'   ') as yjr, " +
                    " GROUP_CONCAT(case when lz.lzzt=2 then u.username end) as tyr, " +
                    " GROUP_CONCAT(case when lz.lzzt=2 then FROM_UNIXTIME(lz.opAt) end) as tysj, " +
                    " GROUP_CONCAT(case when lz.lzzt=3 then u.username end) as ghr, " +
                    " GROUP_CONCAT(case when lz.lzzt=3 then FROM_UNIXTIME(lz.opAt) end) as ghsj " +
                    " from y_jbxx_lzxx lz left join sys_user u on lz.opBy = u.id  where lz.delFlag=0 group by lz.jbxxId " +
                    " ) lzz on j.id = lzz.jbxxId " +
                    " where j.delFlag = 0 and j.lczt>0  "+StringUtil.dataScopeReportJyksBh("j","jyksbh")+")x where 1=1";

            if(StringUtils.isNotBlank(searchKeyword)){
                sql+= " and (ypmc like '%"+searchKeyword+"%' or ypbh like '%"+searchKeyword+"%')";
            }
            if(StringUtils.isNotBlank(bgbh)){
                sql+= " and bgbh like '%"+bgbh+"%' ";
            }
            if(StringUtils.isNotBlank(lczt)){
                sql+= " and lczt = '"+lczt+"'";
            }
            if(StringUtils.isNotBlank(lzzt)){
                sql+= " and lzzt = '"+lzzt+"'";
            }
            if(list.size()>0){
                for(JSONObject object :list){
                    if(object.get("condition").equals("like")){
                        sql+= " and "+object.get("key")+" like '%"+object.get("value")+"%' ";
                    }
                    if(object.get("condition").equals("dropLike")){
                        String values = object.get("value").toString().replace("[\"","").replace("\"]","").replace("[]","").replace("\",\"",",");
                        if(StringUtils.isNotBlank(values)){
                            sql+= " and "+object.get("key")+" like '"+values+"%' ";
                        }
                    }
                    if(object.get("condition").equals("drop")){
                        String values = object.get("value").toString().replace("[\"","").replace("\"]","").replace("[]","").replace("\",\"",",");
                        if(StringUtils.isNotBlank(values)){
                            sql+= " and "+object.get("key")+" = '"+values+"' ";
                        }
                    }
                    if(object.get("condition").equals("number")){
                        sql+= " and "+object.get("key")+" = '"+object.get("value")+"' ";
                    }
                    if(object.get("condition").equals("date")){
                        if(object.get("startDate")!=null && StringUtils.isNotBlank(object.get("startDate").toString())){
                            sql+= " and "+object.get("key")+" >= '"+object.get("startDate")+"' ";
                        }
                        if(object.get("endDate")!=null && StringUtils.isNotBlank(object.get("endDate").toString())){
                            sql+= " and "+object.get("key")+" <= '"+object.get("endDate")+"' ";
                        }
                    }
                }
            }
            if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
                if ("ascending".equalsIgnoreCase(pageOrderBy)) {
                    sql+= " order by "+pageOrderName+" asc,djsj desc ";
                } else {
                    sql+= " order by "+pageOrderName+" desc,djsj desc ";
                }
            }else{
                sql+= " order by djsj desc  ";
            }
            return Result.success().addData(yjbxxService.listPageMap(pageNumber,pageSize, Sqls.create(sql)));
        } catch (Exception e) {
            return Result.error();
        }
    }

    @At
    @Ok("void")
    public void export(@Param("searchKeyword") String searchKeyword, @Param("bgbh") String bgbh,@Param("lczt") String lczt, @Param("lzzt") String lzzt,
                       @Param("listSelect") String listSelect,@Param("colData") String colData,
                       @Param("pageOrderName") String pageOrderName, @Param("pageOrderBy") String pageOrderBy, HttpServletResponse response) {
        try {

            List<JSONObject> list = JSONArray.fromObject(listSelect);
            List<JSONObject> colList = JSONArray.fromObject(colData);

            String sql = " select * from  (select j.id,j.jyks,j.zh,j.yplx,j.ypdj,j.ypzt,j.bysl,j.jysl,j.szcs,j.scrqpc,j.sb,j.ggxh,j.cyryBy,j.cyry,j.cyrq,j.cydbh, " +
                    " j.cydw,j.cyjs,j.cydd,j.jyfdb,j.wtdw,j.wtdwdz,j.wtdwlxr,j.wtdwsj,j.wtdwdh,j.wtdwyb,j.sjdw,j.sjdwdz,j.sjdwlxr,j.sjdwsj, " +
                    " j.sjdwdh,j.sjdwyb,j.scdw,j.scdwdz,j.scdwlxr,j.scdwsj,j.scdwdh,j.scdwyb,j.jyxm,j.jyyj,j.yhxtk,j.jylx,j.jyhth,j.jcfyry, " +
                    " j.dyrq,j.wcqx,j.bz,j.tssm,j.jyrq,j.jyjl,j.jyqksm,j.dyzt,ur.username as djr,FROM_UNIXTIME(j.opAt) as djsj, " +
                    " j.lczt,j.lzzt,j.ypmc,j.ypbh,j.bgbh,j.jyjg,j.bgfsfs,j.status,j.unit," +

                    " lc.bzr,lc.jyry,lc.bzsj,lc.shr,lc.shsj,lc.pzr,lc.pzsj, " +

                    " lzz.yjr,lzz.tyr,lzz.tysj,lzz.ghr,lzz.ghsj " +
                    " from y_jbxx j  " +
                    " left join ( " +
                    " select jbxxId, " +
                    " GROUP_CONCAT(case when lczt=2 then nextspr end) as bzr, " +
                    " GROUP_CONCAT(case when lczt=2 then jyry end) as jyry, " +
                    " GROUP_CONCAT(case when lczt=5 then FROM_UNIXTIME(opAt) end) as bzsj, " +
                    " GROUP_CONCAT(case when lczt=5 then nextspr end) as shr, " +
                    " GROUP_CONCAT(case when lczt=7 then FROM_UNIXTIME(opAt) end) as shsj, " +
                    " GROUP_CONCAT(case when lczt=7 then nextspr end) as pzr, " +
                    " GROUP_CONCAT(case when lczt=9 then FROM_UNIXTIME(opAt) end) as pzsj " +
                    " from y_jbxx_lcxx  where delFlag=0 group by jbxxId " +
                    " ) lc on j.id = lc.jbxxId " +
                    " left join sys_user ur on j.opBy=ur.id  " +
                    " left join ( " +
                    " select lz.jbxxId, " +
                    " GROUP_CONCAT(case when lz.lzzt=1 then concat('移交人：',u.username,'-移交时间：',lz.lzAt,'-接收人：' ,lz.nextspr) end,'   ') as yjr, " +
                    " GROUP_CONCAT(case when lz.lzzt=2 then u.username end) as tyr, " +
                    " GROUP_CONCAT(case when lz.lzzt=2 then FROM_UNIXTIME(lz.opAt) end) as tysj, " +
                    " GROUP_CONCAT(case when lz.lzzt=3 then u.username end) as ghr, " +
                    " GROUP_CONCAT(case when lz.lzzt=3 then FROM_UNIXTIME(lz.opAt) end) as ghsj " +
                    " from y_jbxx_lzxx lz left join sys_user u on lz.opBy = u.id  where lz.delFlag=0 group by lz.jbxxId " +
                    " ) lzz on j.id = lzz.jbxxId " +
                    " where j.delFlag = 0  and j.lczt>0  "+StringUtil.dataScopeReportJyksBh("j","jyksbh")+")x where 1=1";

            if(StringUtils.isNotBlank(searchKeyword)){
                sql+= " and (ypmc like '%"+searchKeyword+"%' or ypbh like '%"+searchKeyword+"%')";
            }
            if(StringUtils.isNotBlank(bgbh)){
                sql+= " and bgbh like '%"+bgbh+"%' ";
            }
            if(StringUtils.isNotBlank(lczt)){
                sql+= " and lczt = '"+lczt+"'";
            }
            if(StringUtils.isNotBlank(lzzt)){
                sql+= " and lzzt = '"+lzzt+"'";
            }
            if(list.size()>0){
                for(JSONObject object :list){
                    if(object.get("condition").equals("like")){
                        sql+= " and "+object.get("key")+" like '%"+object.get("value")+"%' ";
                    }
                    if(object.get("condition").equals("dropLike")){
                        String values = object.get("value").toString().replace("[\"","").replace("\"]","").replace("[]","").replace("\",\"",",");
                        if(StringUtils.isNotBlank(values)){
                            sql+= " and "+object.get("key")+" like '"+values+"%' ";
                        }
                    }
                    if(object.get("condition").equals("drop")){
                        String values = object.get("value").toString().replace("[\"","").replace("\"]","").replace("[]","").replace("\",\"",",");
                        if(StringUtils.isNotBlank(values)){
                            sql+= " and "+object.get("key")+" = '"+values+"' ";
                        }
                    }
                    if(object.get("condition").equals("number")){
                        sql+= " and "+object.get("key")+" = '"+object.get("value")+"' ";
                    }
                    if(object.get("condition").equals("date")){
                        if(object.get("startDate")!=null && StringUtils.isNotBlank(object.get("startDate").toString())){
                            sql+= " and "+object.get("key")+" >= '"+object.get("startDate")+"' ";
                        }
                        if(object.get("endDate")!=null && StringUtils.isNotBlank(object.get("endDate").toString())){
                            sql+= " and "+object.get("key")+" <= '"+object.get("endDate")+"' ";
                        }
                    }
                }
            }
            if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
                if ("ascending".equalsIgnoreCase(pageOrderBy)) {
                    sql+= " order by "+pageOrderName+" asc,djsj desc ";
                } else {
                    sql+= " order by "+pageOrderName+" desc,djsj desc ";
                }
            }else{
                sql+= " order by djsj desc  ";
            }

            Map jylxMap =sysDictService.getCodeMap("jylx");
            Map lcztMap =sysDictService.getCodeMap("lczt");
            Map lzztMap =sysDictService.getCodeMap("lzzt");
            Map zhMap =sysDictService.getIdMap("zh");
            Map yplxMap =sysDictService.getCodeMap("yplx");
            Map xzqhMap =sysDictService.getIdMap("xzqh");
            Map ypczMap =sysDictService.getIdMap("ypcz");
            List<YJbxx> listObj = yjbxxService.listEntity(Sqls.create(sql));
            listObj.stream().map(obj -> {
                if(StringUtils.isNotBlank(obj.getJylx())){
                    obj.setJylx(StringUtil.getDict(obj.getJylx(),jylxMap));
                }
                if(StringUtils.isNotBlank(obj.getLczt())){
                    obj.setLczt(StringUtil.getDict(obj.getLczt(),lcztMap));
                }
                if(StringUtils.isNotBlank(obj.getLzzt())){
                    String statusStr = "";
                    if(StringUtils.isNotBlank(obj.getStatus())&&Integer.valueOf(obj.getLzzt())<3){
                        if(obj.getStatus().equals("0")){
                            statusStr= "(待接收)";
                        }else if(obj.getStatus().equals("1")){
                            statusStr= "(已接收)";
                        }else if(obj.getStatus().equals("-1")){
                            statusStr= "(已拒收)";
                        }
                    }
                    obj.setLzzt(StringUtil.getDict(obj.getLzzt(),lzztMap)+statusStr);
                }
                if(StringUtils.isNotBlank(obj.getZh())){
                    obj.setZh(StringUtil.getDict(obj.getZh(),zhMap));
                }
                if(StringUtils.isNotBlank(obj.getYplx())){
                    obj.setYplx(StringUtil.getDict(obj.getYplx(),yplxMap));
                }
                if(StringUtils.isNotBlank(obj.getSzcs())){
                    obj.setSzcs(StringUtil.getDict(obj.getSzcs(),xzqhMap));
                }
                if(StringUtils.isNotBlank(obj.getYhxtk())){
                    obj.setYhxtk(StringUtil.getDict(obj.getYhxtk(),ypczMap));
                }
                if(StringUtils.isNotBlank(obj.getJysl())){
                    obj.setJysl(obj.getJysl()+" "+(StringUtils.isEmpty(obj.getUnit())?"":obj.getUnit()));
                }
                if(StringUtils.isNotBlank(obj.getBysl())){
                    obj.setBysl(obj.getBysl()+" "+(StringUtils.isEmpty(obj.getUnit())?"":obj.getUnit()));
                }
                if(StringUtils.isNotBlank(obj.getBgfsfs())){
                    if(obj.getBgfsfs().equals("0")){
                        obj.setBgfsfs("邮寄");
                    }else{
                        obj.setBgfsfs("自取");
                    }
                }
                if(StringUtils.isNotBlank(obj.getJyjg())){
                    if(obj.getJyjg().equals("0")){
                        obj.setJyjg("合格");
                    }else if(obj.getJyjg().equals("1")){
                        obj.setJyjg("不合格");
                    }else{
                        obj.setJyjg("不判定");
                    }
                }
                if(StringUtils.isNotBlank(obj.getDyzt())){
                    if(obj.getDyzt().equals("1")){
                        obj.setDyzt("已打印");
                    }else{
                        obj.setDyzt("未打印");
                    }
                }
                return obj;
            }).collect(Collectors.toList());

            //J4EConf j4eConf = new J4EConf();
            J4EConf j4eConf = J4EConf.from(YJbxx.class);
            List<J4EColumn> jcols = new ArrayList<>();
            for(JSONObject column : colList){
                if(column.get("show").toString().equals("true")){
                    J4EColumn j4EColumn = new J4EColumn();
                    j4EColumn.setFieldName(column.get("prop").toString());
                    j4EColumn.setColumnName(column.get("label").toString());
                    j4EColumn.setIgnore(false);
                    jcols.add(j4EColumn);
                }
            }
            j4eConf.setColumns(jcols);
            j4eConf.setSheetName("样品报告"+DateUtil.getDate());
            OutputStream out = response.getOutputStream();
            response.setHeader("content-type", "application/shlnd.ms-excel;charset=utf-8");
            response.setHeader("content-disposition", "attachment; filename=" + new String(j4eConf.getSheetName().getBytes(), "ISO-8859-1") + ".xls");
            J4E.toExcel(out, listObj, j4eConf);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }


}
